package com.wu.dms.dao.sqlProvide;

import org.apache.commons.lang3.StringUtils;
import org.springframework.data.repository.query.Param;

/**
 * @author Cousin
 * @date 2025/1/2
 * @Description
 */
public class StatisticSqlProvider {
    public String selectPlayStatisWithPagination(
            @Param("pageNum") int pageNum,
            @Param("pageSize") int pageSize,
            @Param("searchWord") String searchWord,
            @Param("beginTime") String beginTime,
            @Param("endTime") String endTime) {
        int offset = (pageNum - 1)  * pageSize;
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT song.name as songName, ")
                .append("dms_song_record.valid_play_cnts as validPlayCnts, dms_song_record.play_cnts as playCnts,")
                .append("dms_song_record.date as date ")
                .append("FROM music.song ")
                .append("JOIN music.dms_song_record ON song.id = dms_song_record.song_id ")
                .append("WHERE song.name LIKE #{searchWord} ");
        if (StringUtils.isNotEmpty(beginTime)) {
            sql.append("AND dms_song_record.date >= #{beginTime} ");
        }
        if (StringUtils.isNotEmpty(endTime)) {
            sql.append("AND dms_song_record.date <= #{endTime} ");
        }
        sql.append(" ORDER BY dms_song_record.date DESC");
        sql.append(" LIMIT ").append(offset).append(", #{pageSize}");

        return sql.toString();
    }
    public String countPlayStatisWithPagination(
            @Param("searchWord") String searchWord,
            @Param("beginTime") String beginTime,
            @Param("endTime") String endTime) {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT count(*) as count ")
                .append("FROM music.dms_song_record ")
                .append("JOIN music.song ON song.id = dms_song_record.song_id ")
                .append("WHERE song.name LIKE #{searchWord} ");
        if (StringUtils.isNotEmpty(beginTime)) {
            sql.append("AND dms_song_record.date >= #{beginTime} ");
        }
        if (StringUtils.isNotEmpty(endTime)) {
            sql.append("AND dms_song_record.date <= #{endTime} ");
        }
        return sql.toString();
    }
}
